﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;

/// <summary>
/// Summary description for ShopLaptopConnection
/// </summary>
public class ShopLaptopConnection
{
    SqlConnection conn;
   
    public ShopLaptopConnection()
    {
        conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ShopLaptopConnectionString"].ConnectionString);
	    //
	    // TODO: Add constructor logic here
	    //
    }
    #region Customer
    
    public Customer GetCustomerByUserPass(string user, string pass)
    {
        Customer cus = null;
        SqlCommand cmd = new SqlCommand("Select * From Customer Where CusUser = @u and CusPass = @p and CusStatus=1",conn);
        cmd.Parameters.AddWithValue("@u",user);
        cmd.Parameters.AddWithValue("@p",pass);
        try
        {
            conn.Open();
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                cus = new Customer();
                cus.CusID = rd.GetInt32(0);
                cus.CusUser = rd.GetString(1);
                cus.CusPass = rd.GetString(2);
                cus.CusName = rd.GetString(3);
                cus.CusPhone = rd.GetString(4);
                cus.CusAdd = rd.GetString(5);
                cus.CusEmail = rd.GetString(6);
                cus.CusStatus = rd.GetInt32(7);

            }
        }
        catch (Exception)
        {
            conn.Close();

        }
        finally
        {
            try
            {
                conn.Close();
            }
            catch (Exception)
            {
                
                //throw;
            }
            
        }
        return cus;
    }

    public Customer GetCustomerByEmail(string email)
    {
        Customer cus = null;
        SqlCommand cmd = new SqlCommand("Select * from Customer where cusEmail='"+email+"'",conn);
        conn.Open();
        SqlDataReader rd = cmd.ExecuteReader();
        if (rd.Read())
        {
            cus = new Customer();
            cus.CusID = Convert.ToInt32(rd["CusID"]);
            cus.CusUser = rd["CusUser"].ToString();
            cus.CusPass = rd["CusPass"].ToString();
            cus.CusName = rd["CusName"].ToString();
            cus.CusPhone = "";
            try
            {
                cus.CusPhone = rd["CusPhone"].ToString();
            }
            catch (Exception)
            {
            }
            cus.CusAdd = rd["CusAdd"].ToString();
            cus.CusEmail = rd["CusEmail"].ToString();
            cus.CusStatus = Convert.ToInt32(rd["CusStatus"]);
        }
        conn.Close();
        return cus;
    }

    public int SetCusStatus0(int CusID)
    {
        int check = 0;
        SqlCommand cmd = new SqlCommand("Update Customer Set CusStatus=0 Where CusID="+CusID,conn);
        conn.Open();
        check = cmd.ExecuteNonQuery();
        conn.Close();
        return check;
    }

    public int SetCusStatus1(int CusID)
    {
        int check = 0;
        SqlCommand cmd = new SqlCommand("Update Customer Set CusStatus=1 Where CusID=" + CusID, conn);
        conn.Open();
        check = cmd.ExecuteNonQuery();
        conn.Close();
        return check;
    }

    public int ResetCusPass(int CusID)
    {
        int check = 0;
        SqlCommand cmd = new SqlCommand("Update Customer Set CusPass='123456' Where CusID=" + CusID, conn);
        conn.Open();
        check = cmd.ExecuteNonQuery();
        conn.Close();
        return check;
    }

    public int UpdateCusPassByUsername(string name,string pass)
    {
        int check = 0;
        SqlCommand cmd = new SqlCommand("Update Customer Set CusPass='"+pass+ "'where CusUser='"+name+"'" ,conn);
        conn.Open();
        check = cmd.ExecuteNonQuery();
        conn.Close();
        return check;
    }


    #endregion
    public int InsertOrders(string name, string add, string phone, int payID, int cusID)
    {
        int check = 0;
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = @"Insert into Orders(OrdDate,OrdStatus,OrdName,OrdAdd,OrdPhone,PayID,CusID,OrdDel)
                        values(@date,1,@name,@add,@phone,@payId,@CusId,0)";
        cmd.Parameters.AddWithValue("@date", DateTime.Now);
        cmd.Parameters.AddWithValue("@name", name);
        cmd.Parameters.AddWithValue("@add", add);
        cmd.Parameters.AddWithValue("@phone", phone);
        cmd.Parameters.AddWithValue("@payId", payID);
        cmd.Parameters.AddWithValue("@CusId", cusID);
        try
        {
            conn.Open();
            check = cmd.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception)
        {
            try
            {
                conn.Close();
            }
            catch (Exception)
            {

                throw;
            }
            throw;
        }
        finally
        {
            try
            {
                conn.Close();
            }
            catch (Exception)
            {

                throw;
            }
        }
        
        return check;
    }

    public int InsertOrderDetail(int orderID,int proID,int soluong,float price)
    {
        int check = 0;
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = @"Insert Into orderdetail 
                        values(" + orderID + "," + proID + "," + soluong + "," + price + ",0)";
        
        try
        {
            conn.Open();
            check = cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            try
            {
                conn.Close();
            }
            catch (Exception)
            {
                
                throw;
            }
            throw;
        }
        finally
        {
            conn.Close();
        }

        return check;
     
    }

    public SysAdmin GetAdminByUserPass(string user, string pass)
    {
        SysAdmin ad = null;
        SqlCommand cmd = new SqlCommand("Select * From SysAdmin Where UserAdmin = @u and PassAdmin = @p", conn);
        cmd.Parameters.AddWithValue("@u", user);
        cmd.Parameters.AddWithValue("@p", pass);
        try
        {
            conn.Open();
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                ad = new SysAdmin();
                ad.UserAdmin = rd.GetString(0);
                ad.PassAdmin = rd.GetString(1);

            }
        }
        catch (Exception)
        {
            conn.Close();

        }
        finally
        {
            try
            {
                conn.Close();
            }
            catch (Exception)
            {

                //throw;
            }

        }
        return ad;
    }
    #region Category

    public List<Category> GetAllCategoryByStatus1()
    {
        List<Category> list = null;
        SqlDataAdapter da = new SqlDataAdapter("Select * from category where catestatus=1",conn);
        DataTable dt = new DataTable();
        da.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            list = new List<Category>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Category cate = new Category();
                cate.CateID = Convert.ToInt32(dt.Rows[i]["CateID"]);
                cate.CateName = dt.Rows[i]["CateName"].ToString();
                cate.CateStatus = Convert.ToInt32(dt.Rows[i]["CateStatus"]);
                list.Add(cate);
            }
        }
        return list;
    }

    
    #endregion

    #region Product

    public List<Product> GetProduct0ByCateID(int cateid)
    {
        List<Product> list = null;
        SqlDataAdapter da = new SqlDataAdapter("Select * from Product where cateid="+cateid+" and ProStatus=0",conn);
        DataTable dt = new DataTable();
        da.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            list = new List<Product>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Product pro = new Product();
                pro.ProID = Convert.ToInt32(dt.Rows[i]["ProID"]);
                pro.ProName = dt.Rows[i]["ProName"].ToString();
                pro.ProDesc = dt.Rows[i]["ProDesc"].ToString();
                pro.ProDate = Convert.ToDateTime(dt.Rows[i]["ProDate"]);
                pro.ProPrice = Convert.ToInt32(dt.Rows[i]["ProPrice"]);
                pro.ProImage = "";
                try
                {
                    pro.ProImage = dt.Rows[i]["ProImage"].ToString();
                }
                catch (Exception)
                {
                                     
                }
                pro.ProStatus = Convert.ToInt32(dt.Rows[i]["ProStatus"]);
                pro.ProWarranty = Convert.ToInt32(dt.Rows[i]["ProWarranty"]);
                pro.CateID = Convert.ToInt32(dt.Rows[i]["CateID"]);
                list.Add(pro);
            }
        }
        return list;
    }

    public List<Product> GetProduct1ByCateID(int cateid)
    {
        List<Product> list = null;
        SqlDataAdapter da = new SqlDataAdapter("Select * from Product where cateid=" + cateid + " and ProStatus=1", conn);
        DataTable dt = new DataTable();
        da.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            list = new List<Product>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Product pro = new Product();
                pro.ProID = Convert.ToInt32(dt.Rows[i]["ProID"]);
                pro.ProName = dt.Rows[i]["ProName"].ToString();
                pro.ProDesc = dt.Rows[i]["ProDesc"].ToString();
                pro.ProDate = Convert.ToDateTime(dt.Rows[i]["ProDate"]);
                pro.ProPrice = Convert.ToInt32(dt.Rows[i]["ProPrice"]);
                pro.ProImage = "";
                try
                {
                    pro.ProImage = dt.Rows[i]["ProImage"].ToString();
                }
                catch (Exception)
                {

                }
                pro.ProStatus = Convert.ToInt32(dt.Rows[i]["ProStatus"]);
                pro.ProWarranty = Convert.ToInt32(dt.Rows[i]["ProWarranty"]);
                pro.CateID = Convert.ToInt32(dt.Rows[i]["CateID"]);
                list.Add(pro);
            }
        }
        return list;
    }

    public int SetProductStatus0(int id)
    {
        int check = 0;
        SqlCommand cmd = new SqlCommand("Update Product set prostatus=0 where proid=" + id, conn);
        conn.Open();
        check = cmd.ExecuteNonQuery();
        conn.Close();
        return check;
    }

    public int SetProductStatus1(int id)
    {
        int check = 0;
        SqlCommand cmd = new SqlCommand("Update Product set prostatus=1 where proid=" + id, conn);
        conn.Open();
        check = cmd.ExecuteNonQuery();
        conn.Close();
        return check;
    }

    public int UpdateProductById(int proID,string proName,string proDesc,float proPrice,string proImage,int proWarr,int cateID)
    {
        int check = 0;
        SqlCommand cmd = new SqlCommand(@"Update Product 
                            set proname = @name,prodesc=@desc,proprice=@price,proimage=@image,prowarranty=@warr,cateid=@cate
                            where proID = @proid",conn);
        cmd.Parameters.AddWithValue("@proid", proID);
        cmd.Parameters.AddWithValue("@name", proName);
        cmd.Parameters.AddWithValue("@desc", proDesc);
        cmd.Parameters.AddWithValue("@price", proPrice);
        cmd.Parameters.AddWithValue("@image", proImage);
        cmd.Parameters.AddWithValue("@warr", proWarr);
        cmd.Parameters.AddWithValue("@cate", cateID);
        conn.Open();
        check = cmd.ExecuteNonQuery();
        conn.Close();
        return check;
    }

    public int InsertProduct(string proName, string proDesc,DateTime proDate, float proPrice, string proImage, int proWarr, int cateID)
    {
        int check = 0;
        SqlCommand cmd = new SqlCommand("Insert into Product values(@name,@desc,@date,@price,@image,1,@warr,@cateid)",conn);
        cmd.Parameters.AddWithValue("@name",proName);
        cmd.Parameters.AddWithValue("@desc", proDesc);
        cmd.Parameters.AddWithValue("@date", proDate);
        cmd.Parameters.AddWithValue("@price", proPrice);
        cmd.Parameters.AddWithValue("@image", proImage);
        cmd.Parameters.AddWithValue("@warr", proWarr);
        cmd.Parameters.AddWithValue("@cateid", cateID);
        conn.Open();
        check = cmd.ExecuteNonQuery();
        conn.Close();
        return check;
    }
    #endregion

    
}
